/**
 * Copyright 2005 The Apache Software Foundation
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.edi.db.dialect;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.edi.db.EdiDb;
import com.edi.db.dialect.function.DialectFunction;
import com.edi.db.table.FieldEntity;
import com.edi.db.table.TableEntity;
import com.edi.db.type.NullableType;
import com.edi.util.StringHelper;

public class SybaseDialect extends Dialect {
	private static final Log log = LogFactory.getLog(SybaseDialect.class);

	public SybaseDialect() {
		super();
		registColumnTypes();
	}

	private void registColumnTypes() {
		registerColumnType(Types.BIT, "tinyint"); // Sybase BIT type does not
		// support null values
		registerColumnType(Types.BIGINT, "numeric(19,0)");
		registerColumnType(Types.SMALLINT, "smallint");
		registerColumnType(Types.TINYINT, "tinyint");
		registerColumnType(Types.INTEGER, "int");
		registerColumnType(Types.CHAR, "char(1)");
		registerColumnType(Types.VARCHAR, "varchar($l)");
		registerColumnType(Types.FLOAT, "float");
		registerColumnType(Types.DOUBLE, "double precision");
		registerColumnType(Types.DATE, "datetime");
		registerColumnType(Types.TIME, "datetime");
		registerColumnType(Types.TIMESTAMP, "datetime");
		registerColumnType(Types.VARBINARY, "varbinary($l)");
		registerColumnType(Types.NUMERIC, "numeric($p,$s)");
		registerColumnType(Types.BLOB, "image");
		registerColumnType(Types.CLOB, "text");
		registerColumnType(Types.DECIMAL, "money");
	}

	/**
	 * 获取保存sql，用于批量提交
	 */
	public String getSavePreparedString(List fieldNameist, String tableName) {
		StringBuffer sql = new StringBuffer();
		try {
			sql.append("insert into ").append(tableName).append(" (");
			Iterator it = fieldNameist.iterator();
			while (it.hasNext()) {
				String fieldName = (String) it.next();
				if (!it.hasNext())
					sql.append(fieldName).append(") values (");
				else
					sql.append(fieldName).append(",");
			}
			for (int i = 0; i < fieldNameist.size(); i++) {
				if (i == fieldNameist.size() - 1)
					sql.append("?)");
				else
					sql.append("?,");

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.debug(" return sql =" + sql.toString());
		return sql.toString();
	}

	public String getDeletePreparedString(TableEntity tableEntity) {
		if (tableEntity == null || tableEntity.getTableName() == null
				|| "".equals(tableEntity.getTableName()))
			throw new RuntimeException("表名为空！");
		String sql = "";

		try {
			sql = "delete " + tableEntity.getTableName() + " where ";
			List<String> keys = tableEntity.getPrimarykey();
			Iterator<String> keyIt = keys.iterator();
			while (keyIt.hasNext()) {
				String key = keyIt.next();
				sql += key + " = ? ";
				if (keyIt.hasNext())
					sql += " and " + key + " = ? ";
			}
		} catch (Exception e) {
			e.printStackTrace();

		}
		log.debug(" return sql =" + sql);
		return sql;
	}

	public String ifExitTable(String tableName) {		
		String sql = "select * from sysobjects where type='U' and name ='"+tableName+"'";
		return sql;
	}

	public String createTable(TableEntity tableEntity) {
		// StringBuilder sql= new StringBuilder();
		StringBuffer sql = new StringBuffer();
		try {
			sql.append("CREATE TABLE ").append(tableEntity.getTableName())
					.append(" (");
			Hashtable<String, FieldEntity> fields = tableEntity
					.getFieldEntitys();
			Collection<FieldEntity> set = fields.values();
			Iterator<FieldEntity> it = set.iterator();
			while (it.hasNext()) {
				FieldEntity fieldEntity = it.next();
				if (fieldEntity == null)
					continue;
				sql.append(fieldEntity.getFieldName()).append(" ");
				if (getTypeName(fieldEntity.getFieldType().sqlType()) != null)
					sql.append(getTypeName(
							fieldEntity.getFieldType().sqlType(), fieldEntity
									.getSize(), fieldEntity.getPrecision(),
							fieldEntity.getScale()));// setFieldType(fieldEntity.getFieldType().sqlType(),fieldEntity);
				if (!fieldEntity.isNullable())
					sql.append(" not null");
				else
					sql.append(" null");
				if (fieldEntity.getDefaultValue() != null
						&& !"".equals(fieldEntity.getDefaultValue()))
					sql.append(" default '").append(
							fieldEntity.getDefaultValue()).append("'");
				if (fieldEntity.isIdentify())
					sql.append(" PRIMARY KEY ");
				if (it.hasNext())
					sql.append(",");
				else
					sql.append(")");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return sql.toString();
	}

	//
	// private String setFieldType(int type, FieldEntity field) throws Exception
	// {
	// String typeName = getTypeName(field.getFieldType().sqlType());
	//
	// switch (type) {
	// case Types.VARCHAR:
	// return field.getSize() == 0 ? "varchar" : "varchar("
	// + field.getSize() + ")";
	// default:
	// return typeName;
	// }
	// }

	/**
	 * 返回删除表列的方言语句
	 */
	public String dropColumn(String tableName, List fieldList) {
		if (fieldList == null || fieldList.size() == 0)
			return "";
		String sql = "";
		try {
			sql = " alter table " + tableName + " DROP column  ";
			Iterator it = fieldList.iterator();
			while (it.hasNext()) {
				FieldEntity fieldEntity = (FieldEntity) it.next();
				sql += " " + fieldEntity.getFieldName();
				if (it.hasNext())
					sql += ",";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sql;
	}

	/**
	 * 返回添加表列的方言语句
	 */
	public String addColumn(String tableName, List fieldList) {
		if (fieldList == null || fieldList.size() == 0)
			return "";
		String sql = "";
		try {
			sql = "alter table " + tableName + " add ";
			Iterator it = fieldList.iterator();
			while (it.hasNext()) {
				FieldEntity fieldEntity = (FieldEntity) it.next();
				sql += fieldEntity.getFieldName()
						+ " "
						+ getTypeName(fieldEntity.getFieldType().sqlType(),
								fieldEntity.getSize(), fieldEntity
										.getPrecision(), fieldEntity.getScale());
				if (fieldEntity.isIdentify())
					sql += " PRIMARY KEY";
				else if (!fieldEntity.isNullable()){
					sql += " not null";
					if(StringUtils.isNotBlank(fieldEntity.getDefaultValue()))
						sql +=" default "+fieldEntity.getDefaultValue()+" ";
					else
						sql +=" default '' ";
				}
				if (it.hasNext())
					sql += ",";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.debug(" sql =" + sql);
		return sql;
	}

	/**
	 * 返回修改列的方言语句
	 */
	public String modifyColumn(String tableName, List fieldList) {
		if (fieldList == null || fieldList.size() == 0)
			return "";
		String sql = "";
		try {
			// sql = " ALTER TABLE '" + tableName + "'";
			Iterator it = fieldList.iterator();
			while (it.hasNext()) {
				sql += "alter table " + tableName;
				FieldEntity fieldEntity = (FieldEntity) it.next();
				sql += " ALTER COLUMN " + fieldEntity.getFieldName() + " ";
				if (fieldEntity.getFieldType() != null)
					sql += getTypeName(fieldEntity.getFieldType().sqlType(),
							fieldEntity.getSize(), fieldEntity.getPrecision(),
							fieldEntity.getScale());
				if (!fieldEntity.isNullable())
					sql += "  not null";
				if (it.hasNext())
					sql += ";";
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return sql;
	}

	/**
	 * case when 转换
	 * 
	 * @author cya
	 * 
	 */
	class DecodeFunction extends DialectFunction {
		public String process(String eql, String functionName, String function) {
			StringBuffer sbuffer = new StringBuffer();
			sbuffer.append(eql);
			while (true) {
				int decodeStart = eql.indexOf("decode(");
				if (decodeStart < 0)
					break;
				int decodeEnd = eql.indexOf(")");
				if (eql.length() < 7)
					break;
				eql = eql.substring(decodeStart + 7);
				String parameters = eql.substring(0, decodeEnd);
				if (parameters == null || "".equals(parameters))
					throw new RuntimeException("decode 参数不能为空！");
				String[] paraArray = parameters.split(",");
				if (paraArray.length < 2)
					throw new RuntimeException("decode 参数个数不正确！");
				String sql = "case when ";
				for (int i = 0; i < paraArray.length; i++) {
					if (i == 0)
						sql += paraArray[i] + "= ";
					else if (i == paraArray.length - 1 && i % 2 == 0)
						sql += "else " + paraArray[i];
					else if (i % 2 == 1) {
						sql += paraArray[i] + " ";
					} else if (i % 2 == 0)
						sql += "then " + paraArray[i];
				}
				sql += " end";
				sbuffer.replace(decodeStart, decodeEnd + 1, sql);
			}
			eql = sbuffer.toString();
			return eql;
		}
	}

	/**
	 * 根据方言将eql翻译成sql
	 */
	public String translate(String eql) {
		log.debug(" before translate eql = "+eql);
		log.debug("current ignoreCase : "+ignoreCase);
		if (ignoreCase)
			eql = StringUtils.lowerCase(eql);
		log.debug(" translate eql =" + eql);
		String[] eqls = StringHelper.split(StringHelper.WHITESPACE, eql);
		for (int i = 0; i < eqls.length; i++) {
			String word = eqls[i];
			if (word == null || "".equals(word))
				continue;
			DialectFunction function = getFunction(word);
			if (function != null) {
				function.process(eql, word, eqls[i]);
			}
			int beginBrack = word.indexOf("(");
			if (beginBrack > 0) {
				String functionName = word.substring(0, beginBrack);
				function = getFunction(functionName);
				if (function == null) {
					continue;
				} else {
					function.process(eql, functionName, eqls[i]);
				}
			}
		}
//		log.debug(" sql after translate ="+eql);
		return eql;
	}

	/**
	 * trim 函数 ltrim 和rtrim 的组合 得到trim()
	 * 
	 * @author cya
	 * 
	 */
	class TrimFunction extends DialectFunction {
		StringBuffer sbuffer = new StringBuffer();

		public String process(String eql, String functionName, String function) {
			sbuffer.append(eql);
			int startTrim = eql.toLowerCase().indexOf(function); // 找到这个函数的开始和结束点
			int endTrim = startTrim + function.length();
			if (startTrim > 0) {
				function = "l" + function; // 先加上ltrim
				function = "rtrim(" + function + ")";
				sbuffer.replace(startTrim, endTrim, function);
			}
			eql = sbuffer.toString();
			return eql;

		}

	}

	@Override
	public boolean isEqualType(NullableType type1, NullableType type2) {
		log.debug(" isEqualType compare type1="+type1.sqlType());
		log.debug(" isEqualType compare type2="+type2.sqlType());
		if (type1.sqlType() == type2.sqlType())
			return true;
		switch (type1.sqlType()) {
		case Types.FLOAT:
			if (type2.sqlType() == Types.DOUBLE)
				return true;
		case Types.DOUBLE:
			if (type2.sqlType() == Types.FLOAT)
				return true;
		case Types.BIT:
			if (type2.sqlType() == Types.TINYINT)
				return true;
		case Types.TINYINT:
			if (type2.sqlType() == Types.BIT)
				return true;
		default:
			return type1.sqlType() == type2.sqlType();
		}
	}

	@Override
	public NullableType getSqlType(int type,int precesion,int scale) {
		NullableType rType = EdiDb.STRING;
		switch (type) {
		case Types.LONGVARCHAR:
			rType = EdiDb.LONG;
			break;
		case Types.BINARY:
			rType = EdiDb.BINARY;
			break;
		case Types.BIT:
			rType = EdiDb.BOOLEAN;
			break;
		case Types.BLOB:
			rType = EdiDb.TEXT;
			break;
		case Types.BOOLEAN:
			rType = EdiDb.BOOLEAN;
			break;
		case Types.CHAR:
			rType = EdiDb.CHARACTER;
			break;
		case Types.CLOB:
			rType = EdiDb.TEXT;
			break;
		case Types.DATE:
			rType = EdiDb.DATE;
			break;
		case Types.DECIMAL:
			rType = EdiDb.BIG_DECIMAL;
			break;
		case Types.DOUBLE:
			rType = EdiDb.FLOAT;
			break;
		case Types.FLOAT:
			rType = EdiDb.FLOAT;
			break;
		case Types.INTEGER:
			rType = EdiDb.INTEGER;
			break;
		case Types.TIME:
			rType = EdiDb.TIME;
			break;
		case Types.TIMESTAMP:
			//rType = EdiDb.TIMESTAMP;
			rType = EdiDb.DATE;					//修改成为date 类型， cya 2009-3-27
			break;
		case Types.VARCHAR:
			rType = EdiDb.STRING;
			break;
		case Types.VARBINARY:
			rType = EdiDb.BINARY;
			break;
		case Types.NUMERIC:
			rType = EdiDb.BIG_INTEGER;
			break;
		case Types.TINYINT:
			rType = EdiDb.BOOLEAN;
		}
		return rType;
	}

	@Override
	public List<String> getPrimaryKeys(Connection conn,String tableName) throws SQLException{
		List<String> primaryKeys = new ArrayList<String>();
		DatabaseMetaData dbmeta = conn.getMetaData();

		if (!tableName.equalsIgnoreCase(EdiDb.TABLE_EDI_SYSTEMP)) {
			ResultSet keysResultSet = null;
			try{
			// 获得主键信息
				log.debug("Sybase 主键获取 ，表名为： "+tableName);
				keysResultSet = dbmeta.getPrimaryKeys(null, null, tableName);
			while (keysResultSet.next()) {
				log.debug(keysResultSet.getString(1));
				log.debug(keysResultSet.getString(2));
				log.debug(keysResultSet.getString(3));
				log.debug(keysResultSet.getString(4));
				log.debug(keysResultSet.getString(5));
				log.debug(keysResultSet.getString(6));
				primaryKeys
						.add(keysResultSet.getString("COLUMN_NAME").toLowerCase());
			}
			}finally{
				if (keysResultSet != null){
					keysResultSet.close();
				}
			}
		}
		return primaryKeys;
	}

}
